Grammar
Expressions are used for queries, for defaults of card items, and for
printing expressions into cards. They are set in Database Edit mode; a
normal user does not deal with expressions directly.
Expressions deal with two data types, strings and numbers.
Expressions or sub-expressions returning strings are enclosed in braces;
expressions or sub-expressions returning numbers are enclosed in parentheses.
There are many built-in operators and functions; most of them can be used
only in either string or numeric context.
Numbers begin with a numerical digit or a period, and are in standard
integer, floating-point, or exponential notation. String literals are
enclosed in double quotes. Conversions from numbers to strings use the
%g format (unless printf is used); conversions from strings
to numbers skips leading blanks and converts like atof. Trailing
non-numeric characters are ignored.
Expressions are interpreted, not compiled. This means that all parts of
the expression are evaluated, ?:, &&, and || do not
short-circuit.
In the following tables, n stands for a number ot a numerical expression,
and s stands for a literal string or a string expression. Note that some
operators, such as == and date, appear in both contexts.
Operations
Divisions by zero return 1. Arithmetic operators
use standard C precedences. Bitwise operations operate on 32 bits only.
mmmmmmmmmmm & mmmm & mmmmmmmmmmmmmmmmmmmmmmmmm
Operator & Type & Operation
( n ) & n & Number
{ s } & n & In number context, convert string to a number
n ? n : n & n & If the first number is nonzero, return the second number;
&& otherwise, return the third number
n , n & n & Evaluate both numbers, return second
- n & n & Unary minus
! n & n & Unary boolean NOT
∼ n & n & Unary bitwise NOT
n + n & n & Add two numbers
n - n & n & Subtract two numbers
n * n & n & Multiply two numbers
n / n & n & Divide two numbers
n % n & n & Calculate modulo of two numbers
n & n & n & Calculate bitwise AND of two numbers
n && n & n & Calculate boolean AND of two numbers
n | n & n & Calculate bitwise OR of two numbers
n || n & n & Calculate boolean OR of two numbers
n Î<>n & n & Calculate bitwise XOR of two numbers
n « n & n & Calculate bitwise left shift
n » n & n & Calculate bitwise right shift
n == n & n & 1 if both numbers are equal, 0 otherwise
n != n & n & 1 if both numbers are not equal, 0 otherwise
n < n & n & 1 if the first number is less than the second, 0 otherwise
n > n & n & 1 if the first number is greater than the second, 0 otherwise
n <= n & n & 1 if the first number is less than or equal to the second, 0 otherwise
n >= n & n & 1 if the first number is greater than or equal to the second, 0 otherwise
sqrt ( n ) & n & Square root of a number
exp ( n ) & n & Exponential function, en
log ( n ) & n & Decimal logarithm,
log10n
ln ( n ) & n & Natural logarithm, logen
pow ( n , n ) & n & First number raised to the second, nm
sin ( n ) & n & Sine of a number, sin x
cos ( n ) & n & Cosine of a number, cos x
tan ( n ) & n & Tangent of a number, tan x
asin ( n ) & n & Arc sine of a number,
sin-1x
acos ( n ) & n & Arc cosine of a number,
cos-1x
atan ( n ) & n & Arctangent of a number,
tan-1x
atan2 ( n , n ) & n & Quadrant-aligned arctangent
len ( s ) & n & Length of a string
bound ( n , n , n ) & n & The first number bounded by a minimum (second number) and
&& a maximum (third number)
Operations
Note that string comparisons return strings, and must be enclosed in braces
{} if && or || or other numerical operators are used on the result.
Operator & Type & Operation
{ s } & s & String
( n ) & s & In string context, convert number to a string
s ; s & s & Evaluate both strings, return second
s . s & s & Concatenate strings
s ? s : s & s & If the numeric value of the first string is nonzero, return the second string;
&& otherwise, return the third string
s == s & s & Return "1" if the two strings match; otherwise, return "0"
s != s & s & Return "1" if the two strings do not match; otherwise, return "0"
s < s & s & Return "1" if the first string is lexicographically less than the second
&& string; otherwise, return "0"
s > s & s & Return "1" if the first string is lexicographically greater than the second
&& string; otherwise, return "0"
s <= s & s & Return "1" if the first string is lexicographically less than or equal to the
&& second string; otherwise, return "0"
s >= s & s & Return "1" if the first string is lexicographically greater than or equal to
s in s & s & Return "1" if the first string is contained in the second string; otherwise,
&& return "0"
chop ( s ) & s & Return the string with the trailing newline, if any, removed
substr ( s , n , n ) & s & Return a substring of the first string. The first number is the start index
&& and the second the length. A negative index counts from the end.
printf ( args ) & s & Format and return a string; args is a comma-separated list of expressions.
&& Compound expressions must be enclosed in ( ) or
{ }.
Variables are letters a through z that can hold strings or
numbers. When a variable is assigned to, the result of the assignment is
returned. All variables are reset to the empty string (or 0) when a database
is loaded from disk.
Operator & Type & Operation
var & s ,n & Value of a variable
var = s & s & Assign string value to a variable
var = n & n & Assign numeric value to a variable
var .= s & s & Append string to a variable
var += n & n & Add a number to a variable
var -= n & n & Subtract a number from a variable
var *= n & n & Multiply a variable by a number
var /= n & n & Divide a variable by a number
var %= n & n & Assign modulo with a number to variable
var &= n & n & Perform logical AND with a variable
var |= n & n & Perform logical OR with a variable
var ++ & n & Post-increment variable
var – & n & Post-decrement variable
++ var & n & Pre-increment variable
– var & n & Pre-decrement variable
Access
Database rows (cards) can be accessed by providing an
index in brackets. Without brackets, the current card (this) is
assumed. Database columns are named. The name must always be prefixed
with an underscore (_). In place of the name, the field can be selected
with a column number (which must also be prefixed with an underscore),
beginning at 0. Only fields that store data in the database can be
accessed (types Input, Time, Flag, and Choice);
this excludes fields of type Label and Print.
The avg, dev, min, max, and sum operators
differ from all other operators: they don't reference a field in the
current or any single card, they operate on a field in all cards by
accessing an entire column of the database. These operators are also
available as qavg, qdev, qmin, qmax, and qsum, which apply the calculation only to the result of the last
query (i.e., to the cards displayed in the summary). Finally, the savg, sdev, smin, smax, and qsum variations
are applied to all cards in the current section; if there are no
sections or if all sections are selected, all cards are considered.
The switch statement is legal only in Action when pressed
expressions for Button-type fields in the form editor. It does nothing
except as action for a button in a card. It switches to a new
form as if the Database pulldown had been used (see the Editing Forms
chapter for details about the difference between databases and
forms. The first argument is the new form name, the second argument
is the query expression or search string that determines which cards
are displayed in the summary initially. The possible combinations are:
search("", "")
Does nothing.
search("", "*")
Keep the current form, and put all cards in the summary.
search("", "{expr}")
Keep the current form, and put all cards in the summary for which
expr returns something other than 0 or the empty string.
search("", "(expr)")
Equivalent to the previous, except that the returned string is converted
to a number, which is checked for nonzero values.
search("", "string")
Keep the current form, and put all cards in the summary whose searchable
fields contain string.
search("name", "")
Switch to form name, and display all cards in the summary.
search("name", "xxx")
Switch to form name, and then perform a query. xxx stands
for any of the above query expressions.
Because short-circuiting doesn't work, switch can't depend on a
conditional, but its two arguments can. switch returns the empty
string, which means that the button won't execute a command as usual;
if this is overridden by appending a semicolon and another string
expression, the command is executed after the database switch. To execute
a script before switching, prepend a system statement and a
semicolon to the switch statement (the switch is done after the
expression is completely evaluated). To switch back to the previous
form, use the prevform statement.
Operator & Type & Operation
_field & s ,n & A field from the database, of current card
_field [ n ]& s ,n & A field from the database, from any card
this & n & The number of the current card, 0 is first
last & n & The number of the last card, 0 is first
avg ( _field ) & n & Average of a field in all cards
qavg ( _field ) & n & Average the current query result
savg ( _field ) & n & Average the current section
dev ( _field ) & n & Standard deviation of a field in all cards
qdev ( _field ) & n & Std. dev. of the current query result
sdev ( _field ) & n & Std. dev. of the current section
min ( _field ) & n & Minimum value of a field in all cards
qmin ( _field ) & n & Minimum of the current query result
smin ( _field ) & n & Minimum of the current section
max ( _field ) & n & Maximum value of a field in all cards
qmax ( _field ) & n & Maximum value of the current query result
smax ( _field ) & n & Maximum value of the current section
sum ( _field ) & n & Sum of a field in all cards
qsum ( _field ) & n & Sum of the current query result
ssum ( _field ) & n & Sum of the current section
dbase & s & The name of the accessed database file
form & s & The name of the accessed form file
section & s & The name of the current section, or the empty string
section & n & The number of the section the current card is in, or 0
section [ n ] & s & The name of section n
section [ n ] & n & The number of the section card n is in, or 0
prevform & s & The name of the previous accessed form file
switch ( s , s ) & s & Database switch and/or query; see above
System Access
Operator & Type & Operation
system ( s ) & s & Execute a shell command and return the result as a string
$ envvar & s & Return the value of the environment variable envvar
host & s & The host name of the local host
user & s & The user's login name
uid & n & The user's numeric user ID
gid & n & The user's numeric group ID
access ( s , n ) & n & 1 if the file name exists (if the number is 0), or if it can be accessed
&& for execution (1), writing (2), and/or reading (4). See access(3).
beep & s & Ring the terminal bell, return a null string
error ( args ) & s & Format a string like printf, print it in a window, return a null string
Conversion
Dates and times are stored as number of seconds since
January 1, 1970. Durations are stored as number of seconds. Note that
this means thata time is a significantly larger number than a duration,
even if both have the same hh:mm string representation. The representation
depends on the date and time format selected in the Preferences menu.
Operator & Type & Operation
time & s & The current time as hh:mm or hh:mm[ap] string
time ( n ) & s & Extract time part of the number, and format as hh:mm or hh:mm[ap] string
date & s & Today's date as dd.mm.yy or mm/dd/yy string
date ( n ) & s & Extract date part of the number, and format as dd.mm.yy or mm/dd/yy string
duration ( n ) & s & Convert a number of seconds to a hh:mm string
date & n & Current time in seconds since January 1, 1970
year ( n ) & n & Extract the (four-digit) year from a time
month ( n ) & n & Extract the month 1..12 from a time
day ( n ) & n & Extract the day 1..31 from a time
hour ( n ) & n & Extract the hour 0..23 from a time
minute ( n ) & n & Extract the minute 0..59 from a time
second ( n ) & n & Extract the second 0..59 from a time
julian ( n ) & n & Extract the julian date 0..365 from a time
leap ( n ) & n & 1 if the time is in a leap year, or 0 otherwise